﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SqlAnalysis
{
    public class SqlParser : AnalysisTSQL
    {
        static Regex SpaceRegex = new Regex("[\\s\n\r\t]+", RegexOptions.Compiled);
        static Regex Char1Regex = new Regex(@"(\s,\s|\s,|,\s)", RegexOptions.Compiled);

        private string[] Sqls { get; set; }

        public void Convert(string script)
        {
            // 合并连续空格
            script = SpaceRegex.Replace(script, " ");
            script = Char1Regex.Replace(script, ",");

            // step 1 提取 '' 内容， 清洗 t-sql
            script = this.MatchCharParameter(script);

            // step 2 处理多段 sql, by chat ;
            this.Sqls = this.SplitScript(script);

            foreach (var sql in this.Sqls)
            {

                List<AnalysisOptions> list = new List<AnalysisOptions>();

                // ()
                var result = this.GroupBySqlItem(sql);

                foreach (var item in result)
                {
                    var options = new AnalysisOptions();
                    options.AliasTableMap = new Dictionary<string, string>();
                    var sqlFragment = item.Value.ToString();
                    sqlFragment = sqlFragment.Trim();

                    // 操作动机
                    switch (sqlFragment)
                    {
                        case var fragment when string.Compare(fragment, 0, "SELECT", 0, 6, true) == 0:
                            options.Action = SQLAction.SELECT;
                            break;
                        case var fragment when string.Compare(fragment, 0, "INSERT", 0, 6, true) == 0:
                            options.Action = SQLAction.INSERT;
                            break;
                        case var fragment when string.Compare(fragment, 0, "UPDATE", 0, 6, true) == 0:
                            options.Action = SQLAction.UPDATE;
                            break;
                        case var fragment when string.Compare(fragment, 0, "DELETE", 0, 6, true) == 0:
                            options.Action = SQLAction.DELETE;
                            break;
                        case var fragment when WhereCharMatchRegex.IsMatch(fragment):
                            options.Action = SQLAction.CONDITION;
                            break;
                        default:
                            options.Action = SQLAction.Other;
                            break;
                    }

                    bool isExcuter = !(options.Action == SQLAction.Other || options.Action == SQLAction.CONDITION);
                    string[] splitArr = SqlSplitRegex.Split(sqlFragment).Where(s_item => !string.IsNullOrWhiteSpace(s_item)).ToArray();

                    if (isExcuter)
                    {
                        bool isContainsHaving = sqlFragment.Contains("HAVING");
                        bool isContainsWhere = sqlFragment.Contains("WHERE");
                        bool isContainsOrderBy = sqlFragment.Contains("ORDER BY");
                        bool isContainsGroupBy = sqlFragment.Contains("GROUP BY");
                        bool isContainsJoin = sqlFragment.Contains("JOIN");

                        bool isAnalysisFrom = false;
                        bool isGetAllField = false;

                        for (int i = 1; i < splitArr.Length; i++)
                        {
                            switch (options.Action)
                            {
                                case SQLAction.SELECT:

                                    // 查询参数提取（500ms/1w）
                                    if (!isAnalysisFrom)
                                    {
                                        options.Fields = options.Fields ?? new string[0];
                                        int fromlndex = 0;
                                        int skipFromlndex = 1;
                                        for (int i_s = 2; i_s < splitArr.Length; i_s++)
                                        {
                                            if (string.Compare(splitArr[i_s], "FROM", true) == 0)
                                            {
                                                // 操作对象
                                                base.MapExcuterTableInfo(options, splitArr, (i_s + 1));
                                                options.AliasTableMap.Add(options.TableName, options.Alias);
                                                // Set flag
                                                isAnalysisFrom = true;
                                                fromlndex = i_s;
                                                break;
                                            }
                                            if (options.IsDistinct == false && string.Compare(splitArr[1], "DISTINCT", true) == 0)
                                            {
                                                skipFromlndex += 1;
                                                fromlndex -= 1;
                                                options.IsDistinct = true;
                                            }
                                        }
                                        if (string.Compare(splitArr[options.IsDistinct ? 2 : 1], "TOP", true) == 0)
                                        {
                                            skipFromlndex += 2;
                                            fromlndex -= 2;
                                            options.Top = int.Parse(splitArr[options.IsDistinct ? 3 : 2]);
                                        }
                                        options.Fields = string.Join(" ", splitArr.Skip(skipFromlndex).Take(fromlndex - 1)).Split(',');
                                        if (options.Fields.Contains("*"))
                                        {
                                            isGetAllField = true;
                                            options.Fields = new string[] { "*" };
                                        }
                                    }

                                    // 操作对象
                                    //if (string.Compare(splitArr[i - 1], "from", true) == 0)
                                    //{
                                    //    base.MapExcuterTableInfo(options, splitArr, i);
                                    //    options.AliasTableMap.Add(options.TableName, options.Alias);
                                    //}

                                    // JOIN 操作对象
                                    //if (JoinCharRegex.IsMatch(splitArr[i - 1])) 
                                    if (isContainsJoin && JoinCharArray.Contains(splitArr[i - 1]))
                                    {
                                        if (options.JoinOptions == null)
                                        {
                                            options.JoinOptions = new List<JoinOption>();
                                        }

                                        var joinOption = new JoinOption();
                                        options.JoinOptions.Add(joinOption);

                                        joinOption.JoinType = splitArr[i - 1];
                                        i += base.MapExcuterTableInfo(joinOption, splitArr, i) + 2;
                                        options.AliasTableMap.Add(joinOption.TableName, joinOption.Alias);
                                        //int nextTaglndex = base.FindNextTagIndex(splitArr, i);
                                        var findResult = base.FindNextTagIndexRange(splitArr, i);
                                        //splitArr.Skip(i).Take(nextTagIndex - i).ToArray();
                                        //var where = string.Join(" ", condition);
                                        string[] fields = base.MatchFieldsByCondition(findResult.condition);
                                        if (!isGetAllField)
                                        {
                                            List<string> concats = base.MatchFields(fields);
                                            options.Fields = options.Fields.Concat(concats.ToArray()).Distinct().ToArray();
                                        }
                                        // lastoptions where
                                        joinOption.Wheres = joinOption.Wheres ?? new List<ConditionGroup>();
                                        base.MatchCondition(fields, joinOption.Wheres);
                                    }

                                    // Get Where
                                    if (isContainsWhere)
                                    {
                                        var findTagRangeResult = base.FindNextTagIndexRange(splitArr, i, "WHERE");
                                        if (findTagRangeResult.nextTagIndex > 0)
                                        {
                                            i = findTagRangeResult.nextTagIndex - 1;
                                            string[] fields = base.MatchFieldsByCondition(findTagRangeResult.condition);

                                            if (!isGetAllField)
                                            {
                                                List<string> concats = base.MatchFields(fields);
                                                options.Fields = options.Fields.Concat(concats.ToArray()).Distinct().ToArray();
                                            }

                                            options.Wheres = options.Wheres ?? new List<ConditionGroup>();
                                            base.MatchCondition(fields, options.Wheres);
                                        }
                                    }

                                    // Order By（500ms/1w）
                                    if (isContainsOrderBy)
                                    {
                                        var findTagRangeResultOfOrderBy = base.FindNextTagIndexRange(splitArr, i, "ORDER BY");
                                        if (findTagRangeResultOfOrderBy.nextTagIndex > 0)
                                        {
                                            i = findTagRangeResultOfOrderBy.nextTagIndex - 1;
                                            string orderFields, orderType = "ASC";
                                            if (findTagRangeResultOfOrderBy.condition.Length == 1)
                                            {
                                                orderFields = findTagRangeResultOfOrderBy.condition[0];
                                            }
                                            else
                                            {
                                                orderFields = findTagRangeResultOfOrderBy.condition[0];
                                                orderType = findTagRangeResultOfOrderBy.condition[1];
                                            }
                                            string[] fields = orderFields.Split(',');

                                            if (!isGetAllField)
                                                options.Fields = options.Fields.Concat(fields).Distinct().ToArray();

                                            options.OrderBy = options.OrderBy ?? new SortBy
                                            {
                                                Fields = fields,
                                                OrderType = orderType
                                            };
                                        }
                                    }

                                    // Group by（500ms/1w）
                                    if (isContainsGroupBy)
                                    {
                                        var findTagRangeResultOfGroupBy = base.FindNextTagIndexRange(splitArr, i, "GROUP BY");
                                        if (findTagRangeResultOfGroupBy.nextTagIndex > 0)
                                        {
                                            i = findTagRangeResultOfGroupBy.nextTagIndex - 1;
                                            string orderFields = string.Empty;
                                            if (findTagRangeResultOfGroupBy.condition.Length == 1)
                                            {
                                                orderFields = findTagRangeResultOfGroupBy.condition[0];
                                            }
                                            string[] fields = orderFields.Split(',');

                                            if (!isGetAllField)
                                                options.Fields = options.Fields.Concat(fields).Distinct().ToArray();

                                            options.GroupBy = options.GroupBy ?? new Group
                                            {
                                                Fields = fields
                                            };
                                        }
                                    }

                                    // Having（500ms/1w）
                                    if (isContainsHaving)
                                    {
                                        var findTagRangeResultOfHaving = base.FindNextTagIndexRange(splitArr, i, "HAVING");
                                        if (findTagRangeResultOfHaving.nextTagIndex > 0)
                                        {
                                            i = findTagRangeResultOfHaving.nextTagIndex - 1;
                                            string[] fields = base.MatchFieldsByCondition(findTagRangeResultOfHaving.condition);

                                            if (!isGetAllField)
                                            {
                                                List<string> concats = base.MatchFields(fields);
                                                options.Fields = options.Fields.Concat(concats.ToArray()).Distinct().ToArray();
                                            }

                                            if (options.GroupBy == null)
                                            {
                                                throw new Exception("parent sql error: having");
                                            }
                                            options.GroupBy.Wheres = options.GroupBy.Wheres ?? new List<ConditionGroup>();
                                            base.MatchCondition(fields, options.GroupBy.Wheres);
                                        }
                                    }
                                    break;
                                case SQLAction.UPDATE:
                                    break;
                                case SQLAction.INSERT:
                                    break;
                                case SQLAction.DELETE:
                                    break;
                            }
                        }
                    }
                    else
                    {
                        switch (options.Action)
                        {
                            case SQLAction.CONDITION:
                                string[] fields = base.MatchFieldsByCondition(splitArr);
                                List<string> concats = base.MatchFields(fields);
                                options.Fields = concats.ToArray();
                                options.Wheres = options.Wheres ?? new List<ConditionGroup>();
                                base.MatchCondition(fields, options.Wheres);
                                break;
                            case SQLAction.Other:
                                options.Value = sqlFragment;
                                break;
                        }
                    }

                    list.Add(options);
                }




                string print = string.Empty;
                for (int i = 0; i < list.Count; i++)
                {
                    var item = list[i];

                    switch (item.Action)
                    {
                        case SQLAction.SELECT:

                            string printItem = string.Empty;

                            item.Fields = item.Fields.Select(f => f.Trim()).Distinct().ToArray();

                            printItem += "select ";

                            if (item.IsDistinct)
                                printItem += "distinct ";

                            if (item.Top > 0)
                                printItem += $"top {item.Top} ";

                            if (item.Fields.Count() > 0)
                                printItem += string.Join(",", item.Fields) + " ";

                            printItem += $"from {item.TableName} ";

                            if (!string.IsNullOrWhiteSpace(item.Alias))
                                printItem += $"as {item.Alias} ";

                            if (item.JoinOptions != null)
                            {
                                foreach (var join in item.JoinOptions)
                                {
                                    printItem += $"{join.JoinType} {join.TableName} ";

                                    if (!string.IsNullOrWhiteSpace(join.Alias))
                                        printItem += $"as {join.Alias} ";

                                    printItem += $"on ";

                                    this.FormatConditions(join.Wheres, ref printItem);
                                }
                            }

                            if (item.Wheres?.Any() == true)
                            {
                                printItem += $"where ";
                                this.FormatConditions(item.Wheres, ref printItem);
                            }

                            if (item.OrderBy != null)
                            {
                                printItem += $"order by {(string.Join(",", item.OrderBy.Fields))} {item.OrderBy.OrderType} ";
                            }

                            if (item.GroupBy != null)
                            {
                                printItem += $"group by {(string.Join(",", item.OrderBy.Fields))}";

                                if (item.GroupBy.Wheres?.Any() == true)
                                {
                                    printItem += $" having ";
                                    this.FormatConditions(item.GroupBy.Wheres, ref printItem);
                                }
                            }


                            if (print.Contains($"$SCOPE_{i}"))
                            {
                                print = print.Replace($"$SCOPE_{i}", $"({printItem.Trim()})");
                            }
                            else
                            {
                                print = printItem.Trim();
                            }

                            break;
                        case SQLAction.UPDATE:
                            break;
                        case SQLAction.INSERT:
                            break;
                        case SQLAction.DELETE:
                            break;
                        case SQLAction.CONDITION:
                            string where = string.Empty;
                            this.FormatConditions(item.Wheres, ref where);

                            if (print.Contains($"$SCOPE_{i}"))
                            {
                                print = print.Replace($"$SCOPE_{i}", $"({where.TrimEnd()})");
                            }

                            break;
                        case SQLAction.Other:
                            if (print.Contains($"$SCOPE_{i}"))
                            {
                                print = print.Replace($"$SCOPE_{i}", $"({item.Value})");
                            }
                            break;
                        default:
                            break;
                    }

                    //Console.WriteLine(print+"\n\n");
                }


            }
        }

        void FormatConditions(List<ConditionGroup> Wheres, ref string print)
        {
            foreach (var where in Wheres)
            {
                //print += $"(";

                if (where.Conditions?.Count() > 0)
                {
                    foreach (var condition in where.Conditions)
                    {
                        bool isNullFunction = string.IsNullOrWhiteSpace(condition.LeftFN);

                        if (!isNullFunction)
                            print += $"{condition.LeftFN}(";

                        if (condition.IsLeftScope)
                            print += $"{condition.LeftScope}";
                        else
                        {
                            if (!string.IsNullOrWhiteSpace(condition.LeftAlias))
                                print += $"{condition.LeftAlias}.{condition.LeftField}";
                            else if (!string.IsNullOrWhiteSpace(condition.LeftField))
                                print += $"{condition.LeftField}";
                        }

                        if (!isNullFunction)
                            print += $")";

                        if (!string.IsNullOrWhiteSpace(condition.Logic))
                            print += condition.Logic;


                        isNullFunction = string.IsNullOrWhiteSpace(condition.RightFN);

                        if (!isNullFunction)
                            print += $"{condition.RightFN}(";

                        if (condition.IsRightScope)
                            print += $"{condition.RightScope}";
                        else
                        {
                            if (!string.IsNullOrWhiteSpace(condition.RightAlias))
                                print += $"{condition.RightAlias}.{condition.RightField}";
                            else if (!string.IsNullOrWhiteSpace(condition.RightField))
                                print += $"{condition.RightField}";
                        }

                        if (condition.Logic == " BETWEEN ")
                        {
                            print += $" and ";
                            if (!string.IsNullOrWhiteSpace(condition.RightAlias2))
                                print += $"{condition.RightAlias2}.{condition.RightField2}";
                            else if (!string.IsNullOrWhiteSpace(condition.RightField2))
                                print += $"{condition.RightField2}";
                        }

                        if (!isNullFunction)
                            print += $")";

                        print += $" and ";
                    }

                    print = print.Substring(0, print.Length - 4);
                }

                print = print.Trim();
                //print += $") or ";
                print += $" or ";
            }

            print = print.Substring(0, print.Length - 3);
        }
    }

}
